package com.waji.TableSelectByTCP;

import javafx.application.Application;
import javafx.application.Platform;
import javafx.scene.Scene;
import javafx.scene.control.Label;
import javafx.scene.layout.Pane;
import javafx.stage.Stage;
import javafx.stage.WindowEvent;
import org.json.JSONException;
import org.json.JSONObject;

import java.io.*;
import java.net.InetAddress;
import java.net.ServerSocket;
import java.net.Socket;
import java.sql.*;

/**
 * 每次点击发送，客户端与服务端建立TCP的Socket连接，客户端发送数据库、表、属性，服务端返回属性值的查询结果
 */
public class SelectTableServer extends Application {

    public static void main(String[] args) {
        launch(args);
    }

    @Override
    public void start(Stage primaryStage) {
        Label label = new Label("等待客户端发送！");
        try {
            ServerSocket serverSocket = new ServerSocket(8001, 50, InetAddress.getByName("127.0.0.1"));
            new Thread(() -> {
                // 等待客户端的连接
                while (true) {
                    try {
                        // 服务端接收
                        Socket socket = serverSocket.accept();
                        new Thread(() -> {
                            // 可扩展，message中可通过xml或json传入数据库、表、属性、筛选条件等
                            String message = "";
                            try {
                                // 未收到则后续不执行
                                InputStream inputStream = socket.getInputStream();
                                BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream));
                                String lineMessage;
                                while ((lineMessage = bufferedReader.readLine()) != null) {
                                    message += lineMessage;
                                }

                                // 关闭输入流
                                socket.shutdownInput();

                                JSONObject root = new JSONObject(message);
                                String dbName = root.getString("dbName");
                                String tableName = root.getString("tableName");
                                String columnName = root.getString("columnName");
                                Platform.runLater(() -> label.setText("监听" + dbName + "数据库" + tableName + "表的" + columnName + "属性"));
                                String result;
                                Connection dbConn = CONN(dbName);
                                String sql = "select " + columnName + " from " + tableName;
                                Statement statement = dbConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                                ResultSet resultSet = statement.executeQuery(sql);
                                if (resultSet.next()) {
                                    result = resultSet.getString(columnName);
                                    if (result == null) {
                                        result = "空数据";
                                    }

                                    System.out.println("查询（" + sql + "）结果为：" + result);
                                    // 服务端往客户端发送
                                    OutputStream outputStream = socket.getOutputStream();
                                    PrintWriter printWriter = new PrintWriter(outputStream);
                                    printWriter.write(result);
                                    printWriter.flush();
                                    // 关闭输出流，才能发送
                                    socket.shutdownOutput();
                                }
                            } catch (IOException e) {
                                e.printStackTrace();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            } catch (JSONException e) {
                                e.printStackTrace();
                            }
                        }).start();

                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }).start();
        } catch (IOException e) {
            e.printStackTrace();
        }

        primaryStage.setTitle("SelectTableServer");
        Pane pane = new Pane(label);
        primaryStage.setScene(new Scene(pane, 400, 200));
        primaryStage.setX(100);
        primaryStage.setY(100);
        primaryStage.show();

        primaryStage.addEventHandler(WindowEvent.WINDOW_CLOSE_REQUEST, event -> {
            Platform.exit();
            System.exit(0);
        });
    }

    public static Connection CONN(String dbName) {
        // 加载JDBC驱动
        String driverName = "com.mysql.jdbc.Driver";
        // 连接服务器和数据库
        String dbURL = "jdbc:mysql://127.0.0.1:3306/" + dbName;
        String userName = "root";   //默认用户名
        String userPwd = "root";   //密码
        Connection dbConn = null;
        try {
            Class.forName(driverName);
            dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
            // 如果连接成功 控制台输出Connection Successful!
            System.out.println("Connection Successful!");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dbConn;
    }
}
